/**
 *
 */
package com.honeybees.framework.pagination.dialect.impl;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.honeybees.framework.common.util.StringUtils;

/**
 * <dl>
 * <dt><b> 用于将MySQL数据库的查询语句转换成分页SQL语句和计数（COUNT）SQL语句。 </b></dt>
 * <p>
 * <dd>功能描述</dd>
 * </dl>
 * <p>
 * Copyright (C) All rights reserved.
 * </p>
 *
 * @author 李远明
 * @version 2015-10-23 23:25:27
 * @since 2015年10月23日 下午11:25:27
 */
abstract class MySQLPageHepler {

    /**
     * <DL>
     * <DT><B> 得到分页的SQL语句 </B></DT>
     * <p>
     * <p>
     * <PRE>
     * <DD>SELECT id,houseId,houseMsg,depth,width,height,capacity,stdPrice,rentState,assoRentId,note1,note2 FROM kd_warehouse ORDER BY id ASC</DD>
     * <DD>SELECT id,houseId,houseMsg,depth,width,height,capacity,stdPrice,rentState,assoRentId,note1,note2 FROM kd_warehouse ORDER BY id ASC LIMIT 0,15</DD>
     * </PRE>
     * <p>
     * </DL>
     * <p>
     *
     * @param querySql 查询SQL语句
     * @param offset   偏移量（页码）
     * @param limit    位置（每页显示的数据条数）
     * @return 分页SQL语句
     * @version 2014-09-18 09:41:22
     */
    public static String getPageSQL(String querySql, int offset, int limit) {
        querySql = getLineSql(querySql);

        // String sql = querySelect.replaceAll("[^\\s,]+\\.", "") + " limit " +
        // offset + " ," + limit;
        return querySql + " LIMIT " + offset + "," + limit;
    }

    /**
     * <DL>
     * <DT><B> 得到查询数据条数的SQL语句 </B></DT>
     * <p>
     * <p>
     * <PRE>
     * <DD>SELECT id,houseId,houseMsg,depth,width,height,capacity,stdPrice,rentState,assoRentId,note1,note2 FROM kd_warehouse ORDER BY id ASC</DD>
     * <DD>SELECT COUNT(1) COUNT0  FROM kd_warehouse</DD>
     * </PRE>
     * <p>
     * </DL>
     * <p>
     *
     * @param querySql 查询SQL语句
     * @return 包含COUNT()函数的SQL语句
     * @version 2014-09-18 09:38:14
     */
    public static String getCountSQL(String querySql) {

        querySql = getLineSql(querySql);
        int orderIndex = getLastOrderInsertPoint(querySql);

        int formIndex = getAfterFormInsertPoint(querySql);
        String select = querySql.substring(0, formIndex);

        // FIXME 如果SELECT中包含 DISTINCT，只能在外层包含COUNT。
        if (select.toUpperCase().contains("SELECT DISTINCT") || querySql.toUpperCase().contains("GROUP BY")) {
            return "SELECT COUNT(1) COUNT0 FROM (" +
                    querySql.substring(0, orderIndex) + " ) t";
        } else {
            return "SELECT COUNT(1) COUNT0 " +
                    querySql.substring(formIndex, orderIndex);
        }
    }

    /**
     * <DL>
     * <DT><B> 得到最后一个Order By的插入点位置 </B></DT>
     * <p>
     * <DD>详细介绍</DD>
     * </DL>
     * <p>
     *
     * @param querySql 查询语句
     * @return 最后一个Order By插入点的位置
     * @version 2014-9-18 上午9:39:42
     */
    private static int getLastOrderInsertPoint(String querySql) {
        int orderIndex = querySql.toUpperCase().lastIndexOf("ORDER BY");
        if (orderIndex == -1 || !isBracketMatch(querySql.substring(orderIndex, querySql.length()))) {
            throw new RuntimeException("MySQL分页必须要有ORDER BY语句！");
        }
        return orderIndex;
    }

    /**
     * <DL>
     * <DT><B> 删除SQL语句中的回车、换行符，并且每个单词的间隔都是1个空格。 </B></DT>
     * <p>
     * <DD>功能描述</DD>
     * </DL>
     * <p>
     *
     * @param sql SQL语句
     * @return 如果sql是NULL，返回空；否则返回转化后的SQL。
     */
    private static String getLineSql(String sql) {
        return sql.replaceAll("[\r\n]", " ").replaceAll("\\s{2,}", " ");
    }

    /**
     * 得到SQL第一个正确的FROM的的插入点
     */
    private static int getAfterFormInsertPoint(String querySql) {
        String regex = "\\s+FROM\\s+";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(querySql);
        while (matcher.find()) {
            int idx0 = matcher.start(0);
            String text = querySql.substring(0, idx0);
            if (isBracketMatch(text)) {
                return idx0;
            }
        }
        return 0;
    }

    /**
     * <DL>
     * <DT><B> 判断字符串中括号“()”是否匹配，并不会判断排列顺序是否正确。 </B></DT>
     * <p>
     * <DD>功能描述</DD>
     * </DL>
     * <p>
     *
     * @param text 字符串，要判断的文本
     * @return 如果匹配，返回TRUE，否则返回FALSE
     */
    private static boolean isBracketMatch(String text) {
        return (text != null && (getCharCount(text, '(') == getCharCount(text, ')')));
    }

    /**
     * <DL>
     * <DT><B> 得到字符串<code>text</code>中，<code>ch</code>字符出现的次数。 </B></DT>
     * <p>
     * <DD>功能描述</DD>
     * </DL>
     * <p>
     *
     * @param text 字符串
     * @param ch   字符
     * @return int
     */
    private static int getCharCount(String text, char ch) {
        int count = 0;
        for (int i = 0; i < text.length(); i++) {
            count = (text.charAt(i) == ch) ? count + 1 : count;
        }
        return count;
    }

    public static void main(String[] args) {
        String sql = "SELECT checkid,userno,username,workdate,worktype,workflag,traflag,goalitem,itemplace,addra01,addra02,prnoa,addrb01,addrb02,prnob,addrc01,addrc02,prnoc,note,status,udate,utime,note2,datetype,overtime,latetime,earlytime, startworktime, endworktime,startworkstatus,endworkstatus,fingerstatus1, (CASE WHEN fingerstatus IS NULL THEN '0' WHEN fingerstatus='2' THEN '2' WHEN fingerstatus='3' THEN '3' ELSE '1' END) fingerstatus,(CASE WHEN fingerstatus IS NULL THEN '' WHEN fingerstatus='01' THEN '0' WHEN fingerstatus='02' THEN '1' WHEN fingerstatus='06' THEN '1' ELSE '0' END) startworkstatus1,(CASE WHEN fingerstatus IS NULL THEN '' WHEN fingerstatus='01' THEN '0' WHEN fingerstatus='03' THEN '1' WHEN fingerstatus='06' THEN '1' WHEN fingerstatus='04' THEN '3' ELSE '0' END) endworkstatus1,note1 FROM xr_os_check WHERE userno='111111' AND status='1' AND workdate>='20141001' AND workdate<='20141210' ORDER BY workdate";
        sql = "SELECT id,houseId,houseMsg,depth,width,height FROM kd_warehouse ORDER BY id ASC";
        System.out.println("PageSQL: " + getPageSQL(sql, 0, 15));
        System.out.println("CountSQL: " + getCountSQL(sql));

        String text = "{CHI[与|和] || CAT[J] LOGIC[G|D]}+(2){CAT[A] || OF_AMBI[A]}";
        boolean bracketMatch = isBracketMatch(text);
        System.out.println(bracketMatch);
        bracketMatch = StringUtils.isBracketMatch(text);
        System.out.println(bracketMatch);
    }

}
